﻿using BarcodeDelay.bean;
using Newtonsoft.Json.Linq;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BarcodeDelay.business
{
    public class JTTimeEffi
    {
        public Hashtable m_ht_effibeans = new Hashtable();
        public Hashtable m_ht_site_map = new Hashtable();

        private static JTTimeEffi instance;
        public static JTTimeEffi Instance
        {
            get
            {
                if (instance == null)
                {
                    instance = new JTTimeEffi();
                }
                return instance;
            }
        }

        public System.Data.DataTable GetExcelTableByOleDB(string strExcelPath, string tableName)
        {
            try
            {
                
                System.Data.DataTable dtExcel = new System.Data.DataTable();
                //数据表
                DataSet ds = new DataSet();
                //获取文件扩展名
                string strExtension = System.IO.Path.GetExtension(strExcelPath);
                string strFileName = System.IO.Path.GetFileName(strExcelPath);
                //Excel的连接
                OleDbConnection objConn = null;
                switch (strExtension)
                {
                    case ".xls":
                        objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"");
                        break;
                    case ".XLS":
                        objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"");
                        break;
                    case ".xlsx":
                        objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");//此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)  备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数，"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。 
                        break;
                    case ".XLSX":
                        objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");//此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)  备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数，"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。 
                        break;
                    default:
                        objConn = null;
                        break;
                }
                if (objConn == null)
                {
                    return null;
                }
                objConn.Open();
                //获取Excel中所有Sheet表的信息
                //System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                //获取Excel的第一个Sheet表名
                // string tableName1 = schemaTable.Rows[0][2].ToString().Trim();
                string strSql = "select * from [" + tableName + "$]";
                //获取Excel指定Sheet表中的信息
                OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
                OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
                myData.Fill(ds, tableName);//填充数据
                objConn.Close();
                //dtExcel即为excel文件中指定表中存储的信息
                dtExcel = ds.Tables[tableName];
                return dtExcel;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + "\r\n" + ex.StackTrace);
                return null;
            }
        }


        public void initTimeMap()
        {
            try
            {
                string strExcelPath = System.Environment.CurrentDirectory + "\\config\\" + "运力.xls";
                string tbName = "支线签收时效表（散货）";
                System.Data.DataTable dt = JTTimeEffi.Instance.GetExcelTableByOleDB(strExcelPath, tbName);
                if(dt==null||dt.Rows.Count<10)
                {
                    System.Windows.Forms.MessageBox.Show("initTimeMap error:");
                    return;
                }
                m_ht_effibeans.Clear();
                ArrayList arListAreas = new ArrayList();
                foreach(DataRow dr in dt.Rows)
                {
                    try
                    {
                        string arrSite = dr["接件网点名称"].ToString();
                        string depSite = dr["目的网点名称"].ToString();              
                        string dailiArea = dr["接件代理区"].ToString();
                        string siteCode= dr["接件网点编码"].ToString();
                        string siteDesCode = dr["目的网点编码"].ToString();
                        if (!arListAreas.Contains(dailiArea))
                        {
                            arListAreas.Add(dailiArea);
                        }
                        if(!m_ht_site_map.ContainsKey(arrSite))
                        {
                            m_ht_site_map[arrSite] = siteCode;
                        }
                        if (!m_ht_site_map.ContainsKey(depSite))
                        {
                            m_ht_site_map[depSite] = siteDesCode;
                        }


                        string key = arrSite + "-" + depSite;
                        string keyCode = siteCode + "-" + siteDesCode;
                        string endArrivalTime = dr["最晚到车时间"].ToString();
                        string planDepTime = dr["规划发车时间"].ToString();
                        string planNextStopArrTime = dr["规划到车时间"].ToString();
                        int transferAddDays = Convert.ToInt32(dr["运输跨天"].ToString());
                        int AssignAddDays = Convert.ToInt32(dr["派件跨天"].ToString());
                        TimeBean timeBean = new bean.TimeBean();
                        timeBean.endArrival = endArrivalTime;
                        timeBean.endDepart = planDepTime;
                        timeBean.planArriveNextStie = planNextStopArrTime;
                        timeBean.transferAddDays = transferAddDays;
                        timeBean.assignAddDays = AssignAddDays;
                        if (!m_ht_effibeans.ContainsKey(keyCode))
                        {
                            ArrayList arListBeans = new ArrayList();
                            arListBeans.Add(timeBean);
                            m_ht_effibeans[keyCode] = arListBeans;
                        }
                        else
                        {
                            ArrayList arListBeans = m_ht_effibeans[keyCode] as ArrayList;
                            arListBeans.Add(timeBean);
                        }
                    }
                    catch(Exception ex1)
                    {
                        System.Windows.Forms.MessageBox.Show("initTimeMap ex1:" + ex1.ToString());
                    }
                    
                }

            }
            catch(Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("initTimeMap ex:"+ex.ToString());
            }
        }
       

    }
}
